package ecustom.common.conn;

import java.io.BufferedReader;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Clob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;

import org.apache.log4j.Logger;

import ecustom.common.CustomMap;
import ecustom.common.util.CustomUtil;
import weaver.conn.ConnectionPool;
import weaver.conn.FastConnBuffer;
import weaver.conn.FastConnNode;
import weaver.conn.WeaverConnection;
import weaver.general.GCONST;

/**
 * DB 工具类。
 * 法律声明：非公开代码，仅供授权机构（公司或个人）使用，公开发表将追究法律责任。
 * @author 曹水涛
 */
public final class DBUtil {
	
	private static final Logger log = Logger.getLogger(DBUtil.class);

	/**
	 * 获取数据库连接。
	 * @param dsName
	 * @return
	 * @throws SQLException
	 */
	public static WeaverConnection getConnection(String dsName) throws SQLException {
		// 如果是默认数据库，则直接创建数据路连接并返回
		if (CustomUtil.isBlank(dsName) || dsName.equals(GCONST.getServerName())) {
			return ConnectionPool.getInstance().getConnection();
		}
		
		// 启动缓存
		if (!FastConnBuffer.isBuffered()) FastConnBuffer.start();

		// 如果缓存中的数据库连接没有关闭，则直接返回缓存中的数据库连接
		FastConnNode connNode = FastConnBuffer.getConnNode(dsName);
		if (connNode != null && !connNode.getConnection().isClosed()) {
			return connNode.getConnection();
		}
		
		// 创建数据路连接并添加到缓存中
		WeaverConnection conn = ConnectionPool.getInstance().getConnection(dsName);
		connNode = new FastConnNode(conn, System.currentTimeMillis());
		FastConnBuffer.setConnNode(dsName, connNode);
		
		return connNode.getConnection();
	}

	public static String clobToString(Clob clob) throws SQLException { 
		java.io.Reader is = clob.getCharacterStream();// 得到流 
		BufferedReader br = new BufferedReader(is); 
		StringBuffer sb = new StringBuffer(); 
		try {
			String s = br.readLine(); 
			while (s != null) {	// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING 
				sb.append(s); 
				s = br.readLine(); 
			} 
		} catch (Exception e) {
			log.error("clobToString() error", e);
		}
		
		return sb.toString(); 
	}
	
	public static void setParameters(PreparedStatement stmt, Object... values) throws SQLException {
		if (values != null && values.length > 0) {
			for (int i = 0; i < values.length; i++) {
				setParameter(stmt, (i + 1), values[i]);
			}
		}
	}
	
	public static void setParameter(PreparedStatement stmt, int index, Object value) throws SQLException {
		if (value == null) {
			stmt.setNull(index, Types.NULL);
		} else if (value instanceof String) {
			stmt.setString(index, (String)value);
		} else if (value instanceof Integer) {
			stmt.setInt(index, (Integer)value);
		} else if (value instanceof Double) {
			stmt.setDouble(index, (Double)value);
		} else if (value instanceof byte[]) {
			stmt.setBytes(index, (byte[])value);
		} else if (value instanceof Boolean) {
			stmt.setBoolean(index, (Boolean)value);
		} else if(value instanceof Float){
			stmt.setFloat(index, (Float)(value));
		} else if(value instanceof BigDecimal){
			stmt.setDouble(index, ((BigDecimal)value).doubleValue());
		} else if(value instanceof Timestamp){
			stmt.setTimestamp(index, (Timestamp)value);
		} else if(value instanceof Long){
			stmt.setLong(index, (Long)value);
		} else {
			throw new SQLException("使用了没有处理过的字段类型 - " + value.getClass().getName());
		}
	}
	
	public static void returnConnection(DB db) throws SQLException {
		if (db.getResultSet() != null) {try {db.getResultSet().close();} catch (SQLException e) {e.printStackTrace();}}
		if (db.getStatement() != null) try {db.getStatement().close();} catch (SQLException e) {e.printStackTrace();}
		if (db.getConnection() != null) { ConnectionPool.getInstance().returnConnection(db.getSource(), db.getConnection()); }
	}
	
	public static void returnConnection(ResultSet rs, Statement stmt, WeaverConnection conn) {
		if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}
		if (stmt != null) try {stmt.close();} catch (SQLException e) {e.printStackTrace();}
		if (conn != null) { ConnectionPool.getInstance().returnConnection(GCONST.getServerName(), conn); }
	}
	
	public static void returnConnection(ResultSet rs, Statement stmt, WeaverConnection conn, String dsName) {
		if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}
		if (stmt != null) try {stmt.close();} catch (SQLException e) {e.printStackTrace();}
		if (conn != null) { ConnectionPool.getInstance().returnConnection(dsName, conn); }
	}

	public static String getIdFieldName(Class<?> clazz) {
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			Id id = field.getAnnotation(Id.class);
			Column column = field.getAnnotation(Column.class);
			if (id != null) {
				String fieldName = column == null ? field.getName() : column.name();
				return fieldName;
			}
		}
		throw new RuntimeException("没有定义主键字段，entity class = " + clazz.getName());
	}
	
	public static Object getIdFieldValue(Object obj) throws Exception {
		String idFieldName = getIdFieldName(obj.getClass());
		String getIdMethod = "get" + CustomUtil.upperFirstChar(idFieldName);
		return obj.getClass().getMethod(getIdMethod).invoke(obj);
	}

	public static GenerationType getIdGenerationType(Class<?> clazz) {
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			Id id = field.getAnnotation(Id.class);
			if (id != null) {
				GeneratedValue gv = field.getAnnotation(GeneratedValue.class);
				if (gv == null) {
					throw new RuntimeException("主键策略未定义，entity class =  " + clazz.getName());
				}
				return gv.strategy();
			}
		}
		throw new RuntimeException("没有定义主键字段，entity class = " + clazz.getName());
	}

	public static GenerationType getIdGenerationType(Field field) {
		Id id = field.getAnnotation(Id.class);
		GeneratedValue gv = field.getAnnotation(GeneratedValue.class);
		return id != null && gv != null ? gv.strategy() : null;
	}
	
	public static <T> String getInsertSql(Set<String> fieldNames, Object o) {
		String tableName = DBUtil.getTableName(o.getClass());
		StringBuilder sql = new StringBuilder("INSERT INTO ").append(tableName).append("(");
		StringBuilder fieldNameStr = new StringBuilder();
		StringBuilder fieldValueStr = new StringBuilder();
		for (String fieldName : fieldNames) {
			fieldNameStr.append(", ").append(fieldName);
			fieldValueStr.append(", ?");
		}
		sql.append(fieldNameStr.substring(2));
		sql.append(") VALUES(").append(fieldValueStr.substring(2)).append(")");
		return sql.toString();
	}
	
	public static <T> String getInsertSql(String tableName, Set<String> fieldNames) {
		StringBuilder sql = new StringBuilder("INSERT INTO ").append(tableName).append("(");
		StringBuilder fieldNameStr = new StringBuilder();
		StringBuilder fieldValueStr = new StringBuilder();
		fieldNames.forEach(v -> {
			fieldNameStr.append(", ").append(v);
			fieldValueStr.append(", ?");
		});
		sql.append(fieldNameStr.substring(2));
		sql.append(") VALUES(").append(fieldValueStr.substring(2)).append(")");
		return sql.toString();
	}
	
	public static <T> String getUpdateSql(Set<String> fieldNames, Object o, String idFieldName) {
		String tableName = DBUtil.getTableName(o.getClass());
		StringBuilder sql = new StringBuilder("UPDATE ").append(tableName).append(" SET ");
		StringBuilder fields = new StringBuilder();
		for (String fieldName : fieldNames) {
			fields.append(", ").append(fieldName).append("=?");
		}
		sql.append(fields.substring(2));
		sql.append(" WHERE ").append(idFieldName).append("=?");
		return sql.toString();
	}
	
	public static <T> String getQuerySql(Class<T> clazz, String whereSql) {
		CustomMap queryFields = getQueryFields(clazz);
		String queryFieldsSql = getQueryFiledsSql(queryFields);
		StringBuilder sql = new StringBuilder("SELECT ").append(queryFieldsSql);
		sql.append(" FROM ").append(getTableName(clazz));
		if (whereSql != null && !whereSql.isEmpty()) {
			sql.append(" WHERE ").append(whereSql);
		}
		return sql.toString();
	}

	public static String getTableName(Class<?> clazz) {
		Table table = clazz.getAnnotation(Table.class);
		if (table != null && CustomUtil.isNotBlank(table.name())) {
			return table.name();
		}
		return clazz.getSimpleName();
	}

	public static String getDsName(Class<?> clazz) {
		Table table = clazz.getAnnotation(Table.class);
		if (table != null && CustomUtil.isNotBlank(table.catalog())) {
			return table.catalog();
		}
		return GCONST.getServerName();
	}
	
	public static CustomMap getQueryFields(Class<?> clazz) {
		CustomMap queryFields = new CustomMap();
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			Column column = field.getAnnotation(Column.class);
			Transient t = field.getAnnotation(Transient.class);
			if (t != null) {
				continue ;
			}
			if (column == null) {
				queryFields.put(field.getName().toLowerCase(), field);
			} else {
				queryFields.put(column.name().toLowerCase(), field);
			}
		}
		return queryFields;
	}
	
	public static String getQueryFiledsSql(CustomMap queryFields) {
		if (queryFields.isEmpty()) {
			return "";
		}
		StringBuilder sql = new StringBuilder();
		for (String fieldName : queryFields.keySet()) {
			sql.append(", ").append(fieldName);
		}
		return sql.toString().substring(2);
	}

	public static CustomMap getSaveFields(Object o) throws Exception {
		CustomMap saveFields = new CustomMap();

		Field[] fields = o.getClass().getDeclaredFields();
		for (Field field : fields) {
			
			// 如果不是持久化字段，不需要保存
			Column column = field.getAnnotation(Column.class);
			Transient t = field.getAnnotation(Transient.class);
			if (t != null) continue ;
			
			// 如果是ID字段，并且是自增列，不需要保存
			GenerationType gvType = getIdGenerationType(field);
			if (gvType != null && gvType == GenerationType.IDENTITY) continue ;
			
			String getMethodName = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
			Object value = o.getClass().getMethod(getMethodName).invoke(o);
			String dbFieldName = column == null ? field.getName() : column.name();
			saveFields.put(dbFieldName.toUpperCase(), value);
		}
		return saveFields;
	}
	
	public static <T> T toObject(CustomMap map, Class<T> clazz, CustomMap queryFields) throws Exception {
		T o = clazz.newInstance();
		for (String colName : map.keySet()) {
			Field field = (Field)queryFields.get(colName.toLowerCase());
			
			// 如果数据库查询结果中的列名在实体类中找不到，跳过本次循环
			if (field == null) {
				continue ;
			}
			
			String setMethodName = "set" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
			if (field.getType().equals(Integer.class)) {
				clazz.getMethod(setMethodName, field.getType()).invoke(o, CustomUtil.getInteger(map.get(colName)));
			} else if (field.getType().equals(Double.class)) {
				clazz.getMethod(setMethodName, field.getType()).invoke(o, CustomUtil.getDouble(map.get(colName)));
			} else if (field.getType().equals(String.class)) {
				clazz.getMethod(setMethodName, field.getType()).invoke(o, CustomUtil.getString(map.get(colName)));
			}
		}
		return o;
	}
	
	public static <T> T toObject(ResultSet rs, Class<T> clazz, CustomMap queryFields) throws Exception {
		if (clazz == String.class) {
			return (T)rs.getString(1);
		}
		
		if (clazz == Integer.class) {
			return (T)CustomUtil.getInteger(rs.getString(1));
		}
		
		if (clazz == Double.class) {
			return (T)CustomUtil.getDouble(rs.getString(1));
		}
		
		if (clazz == Object.class) {
			return (T)toMap(rs);
		}
		
		T o = clazz.newInstance();
		int colCount = rs.getMetaData().getColumnCount();
		for (int i = 0; i < colCount; i++) {
			String colName = rs.getMetaData().getColumnName(i + 1);
			Field field = (Field)queryFields.get(colName.toLowerCase());
			
			// 如果数据库查询结果中的列名在实体类中找不到，跳过本次循环
			if (field == null) {
				continue ;
			}
			
			String setMethodName = "set" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
			if (field.getType().equals(Integer.class)) {
				clazz.getMethod(setMethodName, field.getType()).invoke(o, CustomUtil.getInteger(rs.getString(colName), null));
			} else if (field.getType().equals(Double.class)) {
				clazz.getMethod(setMethodName, field.getType()).invoke(o, CustomUtil.getDouble(rs.getString(colName), null));
			} else if (field.getType().equals(String.class)) {
				clazz.getMethod(setMethodName, field.getType()).invoke(o, rs.getString(colName));
			}
		}
		return o;
	}
	
	public static CustomMap toMap(ResultSet rs) throws SQLException  {
		CustomMap row = new CustomMap();
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();
		for (int i = 1; i <= count; i++) {
			Object obj = rs.getObject(i);
			if (obj != null && obj instanceof Clob)
				row.put(rsmd.getColumnName(i).toUpperCase(), clobToString((Clob)obj));
			else
				row.put(rsmd.getColumnName(i).toUpperCase(), rs.getObject(i));
		}
		return row;
	}
	
	public static String joinValues(Object... values) {
		String valueStr = "";
		for (Object v : values) {
			valueStr += ", " + v;
		}
		return valueStr.length() > 0 ? valueStr.substring(2) : "";
	}
	
	public static String setEmpCode(String code) {
		while(code.length() < 8) {
			code = "0" + code;
		}
		return code;
	}
	
}